package com.df.springmvc.supper;

import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate4.support.HibernateDaoSupport;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import com.common.bean.SQLBean;
import com.common.bean.SQLInsertBean;
import com.common.bean.SQLResultBean;
import com.common.bean.SQLUpdateBean;
import com.df.springmvc.common.bean.CurrentUserBean;
import com.df.springmvc.common.bean.FieldBean;

public abstract class SupperDao extends HibernateDaoSupport implements
		SupperDaoI {
	public String path;
	public String webPath;
	{
		this.getClass().getResource("/").getHost();
		path = URLDecoder.decode(SupperDao.class.getClassLoader()
				.getResource("").getPath());
		webPath = path.replace("\\", "/").replace("/WEB-INF/classes", "");
	}
	public DataSource dataSource;
	Session session = null;
	/**
	 * 软件工具库数据源
	 */
	public DataSource dataSource2;
	Session session2 = null;

	public DataSource getDataSource2() {
		return dataSource2;
	}

	public void setDataSource2(DataSource dataSource2) {
		this.dataSource2 = dataSource2;
	}

	public Session getSession2() {
		return session2;
	}

	public void setSession2(Session session2) {
		this.session2 = session2;
	}

	public void setSession(Session session) {
		this.session = session;
	}

	protected Object querySql(Object sql, Object[] attribute, int first, int end)
			throws Exception {
		ArrayList<Object> list = null;
		if (sql == null) {
			return null;
		} else {
		}
		if (attribute == null) {
			attribute = new Object[] {};
		}
		try {
			Query query = getSessionFactory().getCurrentSession()
					.createSQLQuery(String.valueOf(sql));
			for (int i = 0; i < attribute.length; i++) {
				query.setParameter(i, attribute[i]);
			}
			if (end - first > 0) {
				query.setFirstResult(first);
				;
				query.setMaxResults(end - first);
				;
			}
			list = (ArrayList<Object>) query.list();
			org.apache.log4j.Logger.getLogger(getClass()).info(
					"ReturnCount:" + list.size());
			return list;
		} catch (Exception e) {
			throw e;
		} finally {
		}
	}

	protected Object querySql(Object sql, HashMap<String, Object> attribute,
			int first, int end) throws Exception {
		ArrayList<Object> list = null;
		if (sql == null) {
			return null;
		} else {
		}
		if (attribute == null) {
			attribute = new HashMap<String, Object>() {
			};
		}
		try {
			Query query = getSessionFactory().getCurrentSession()
					.createSQLQuery(String.valueOf(sql));
			for (String key : attribute.keySet()) {
				query.setParameter(key, attribute.get(key));
			}
			if (end - first > 0) {
				query.setFirstResult(first);
				;
				query.setMaxResults(end - first);
				;
			}
			list = (ArrayList<Object>) query.list();
			org.apache.log4j.Logger.getLogger(getClass()).info(
					"ReturnCount:" + list.size());
			return list;
		} catch (Exception e) {
			throw e;
		} finally {
		}
	}

	/**
	 * 通过?+数组方式传入参数
	 * 
	 * @param sql
	 * @param attribute
	 * @return
	 * @throws Exception
	 */
	protected Object updateSql(Object sql, Object[] attribute) throws Exception {
		// TODO Auto-generated method stub
		String rs = "";
		try {
			Query query = getSessionFactory().getCurrentSession()
					.createSQLQuery(String.valueOf(sql));
			for (int i = 0; i < attribute.length; i++) {
				query.setParameter(i, attribute[i]);
			}
			rs = String.valueOf(query.executeUpdate());
		} catch (Exception e) {
			throw e;
		} finally {
		}
		return rs;
	}

	/**
	 * 通过map进行传入参数
	 * 
	 * @param sql
	 * @param attribute
	 * @return
	 * @throws Exception
	 */
	protected Object updateSql(Object sql, HashMap<String, Object> attribute)
			throws Exception {
		// TODO Auto-generated method stub
		String rs = "";
		try {
			Query query = getSessionFactory().getCurrentSession()
					.createSQLQuery(String.valueOf(sql));
			for (String key : attribute.keySet()) {
				query.setParameter(key, attribute.get(key));
			}
			rs = String.valueOf(query.executeUpdate());
		} catch (Exception e) {
			throw e;
		} finally {
		}
		return rs;
	}

	/**
	 * 实现更新动作
	 * 
	 * @param sqlBean
	 * @return
	 * @throws Exception
	 */
	protected Object excuteSqlBean(SQLBean sqlBean) throws Exception {
		if (sqlBean instanceof SQLInsertBean) {
			SQLInsertBean sQLInsertBean = (SQLInsertBean) sqlBean;
			HashMap<String, Object> columnMap = sQLInsertBean.getColumnMap();
			String sql = "insert into #0#(#1#) values(#2#)";
			ArrayList<String> column = new ArrayList<String>();
			ArrayList<String> valueKey = new ArrayList<String>();
			ArrayList<Object> valueValue = new ArrayList<Object>();
			org.apache.log4j.Logger.getLogger(getClass()).debug(
					"columnMap:" + columnMap);
			for (String key : columnMap.keySet()) {
				if (columnMap.get(key) == null) {
					// column.add(key);
					// valueKey.add("null");
					// valueValue.add(columnMap.get(key));
				} else {
					column.add(key);
					valueKey.add("?");
					valueValue.add(columnMap.get(key));
				}
			}
			sql = sql.replace("#0#", sqlBean.getTableName());
			sql = sql.replace(
					"#1#",
					("#" + column + "#").toString().replace("#[", "")
							.replace("]#", "").replace(" ,", ","));
			sql = sql.replace("#2#",
					("#" + valueKey + "#").toString().replace("#[", "")
							.replace("]#", "").replace(" ,", ","));
			return updateSql(sql, valueValue.toArray());
		} else if (sqlBean instanceof SQLUpdateBean) {
			SQLUpdateBean SQLUpdateBean = (SQLUpdateBean) sqlBean;
			HashMap<String, Object> columnMap = SQLUpdateBean.getColumnMap();
			HashMap<String, Object> conditionMap = SQLUpdateBean
					.getConditionMap();
			String sql = "update #0# set #1# where 1=1 #2# ";
			ArrayList<String> set = new ArrayList<String>();
			ArrayList<Object> condition = new ArrayList<Object>();
			ArrayList<Object> valueValue = new ArrayList<Object>();
			org.apache.log4j.Logger.getLogger(getClass()).debug(
					"columnMap:" + columnMap);
			for (String key : columnMap.keySet()) {
				if (columnMap.get(key) == null) {
					set.add(key + "=null");
				} else {
					set.add(key + "=?");
					valueValue.add(columnMap.get(key));
				}
			}
			for (String key : conditionMap.keySet()) {
				if (conditionMap.get(key) == null) {
					condition.add("and " + key + " is null");
				} else {
					condition.add("and " + key + "=?");
					valueValue.add(conditionMap.get(key));
				}
			}
			sql = sql.replace("#0#", sqlBean.getTableName());
			sql = sql.replace(
					"#1#",
					("#" + set + "#").toString().replace("#[", "")
							.replace("]#", ""));
			sql = sql.replace("#2#", ("#" + condition + "#").toString()
					.replace("#[", "").replace("]#", "").replace(",", ""));
			if (SQLUpdateBean.getCustomCondition() != null) {
				sql = sql.replace("1=1",
						"1=1 and " + SQLUpdateBean.getCustomCondition());
			}
			// org.apache.log4j.Logger.getLogger(getClass()).info("#" + set +
			// "#".toString().replace("#[",""));
			// System.out.println(sql);
			return updateSql(sql, valueValue.toArray());
		} else {
			throw new Exception("sqlBean 的类型不正确");
		}
	}

	/**
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	protected SQLResultBean querySqlToSQLResultBean(String sql)
			throws Exception {
		ArrayList<String> headerList = new ArrayList<String>();
		ArrayList<String> typeList = new ArrayList<String>();
		querySqlColumnInfo(String.valueOf(sql), headerList, typeList);
		SQLResultBean sqlResultBean = new SQLResultBean();
		sqlResultBean.setHeaderList(headerList);
		sqlResultBean.setTypeList(typeList);
		sqlResultBean.setList(querySql(sql, new Object[] {}, 0, 0));
		return sqlResultBean;
	}

	protected SQLResultBean querySqlToSQLResultBean(String sql, int first,
			int end) throws Exception {
		ArrayList<String> headerList = new ArrayList<String>();
		ArrayList<String> typeList = new ArrayList<String>();
		querySqlColumnInfo(String.valueOf(sql), headerList, typeList);
		SQLResultBean sqlResultBean = new SQLResultBean();
		sqlResultBean.setHeaderList(headerList);
		sqlResultBean.setTypeList(typeList);
		sqlResultBean.setList(querySql(sql, new Object[] {}, first, end));
		return sqlResultBean;
	}

	protected SQLResultBean querySqlToSQLResultBean(String sql,
			Object[] attribute) throws Exception {
		ArrayList<String> headerList = new ArrayList<String>();
		ArrayList<String> typeList = new ArrayList<String>();
		querySqlColumnInfo(String.valueOf(sql), headerList, typeList);
		SQLResultBean sqlResultBean = new SQLResultBean();
		sqlResultBean.setHeaderList(headerList);
		sqlResultBean.setTypeList(typeList);
		sqlResultBean.setList(querySql(String.valueOf(sql), attribute, 0, 0));
		return sqlResultBean;
	}

	/**
	 * @param sql
	 * @param attribute
	 *            对应参数数组类型
	 * @param first
	 * @param end
	 * @return
	 * @throws Exception
	 */
	protected SQLResultBean querySqlToSQLResultBean(String sql,
			Object[] attribute, int first, int end) throws Exception {
		ArrayList<String> headerList = new ArrayList<String>();
		ArrayList<String> typeList = new ArrayList<String>();
		querySqlColumnInfo(sql, headerList, typeList);
		SQLResultBean sqlResultBean = new SQLResultBean();
		sqlResultBean.setHeaderList(headerList);
		sqlResultBean.setTypeList(typeList);
		sqlResultBean.setList(querySql(sql, attribute, first, end));
		return sqlResultBean;
	}

	/**
	 * @param sql
	 * @param tmp
	 *            对应参数Map类型
	 * @param first
	 * @param end
	 * @return
	 * @throws Exception
	 */
	protected SQLResultBean querySqlToSQLResultBean(String sql,
			HashMap<String, Object> tmp, int first, int end) throws Exception {
		// TODO Auto-generated method stub
		ArrayList<String> headerList = new ArrayList<String>();
		ArrayList<String> typeList = new ArrayList<String>();
		querySqlColumnInfo(sql, headerList, typeList);
		SQLResultBean sqlResultBean = new SQLResultBean();
		sqlResultBean.setHeaderList(headerList);
		sqlResultBean.setTypeList(typeList);
		sqlResultBean.setList(querySql(sql, tmp, first, end));
		return sqlResultBean;
	}

	private void querySqlColumnInfo(String sql, List<String> headerList,
			List<String> typeList) throws Exception {
		Connection connection = dataSource.getConnection();
		int ii = 0;
		while (sql.contains(":") && ii++ < 100) {
			sql = sql.replaceFirst("\\:\\S*", "\\?");
		}
		PreparedStatement ps = connection.prepareStatement(String.valueOf(sql)
				.replace("where", "where 1=2 and "));
		if (headerList != null) {
			headerList.clear();
		}
		if (typeList != null) {
			typeList.clear();
		}
		try {
			int index = 0;
			while (sql.contains("?") && index++ < 100) {
				sql = sql.replaceFirst("\\?", "");
				// org.apache.log4j.Logger.getLogger(getClass()).info("temp:" +
				// temp);
				ps.setObject(index, "");
			}
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int colCount = metaData.getColumnCount();
			for (int i = 0; i < colCount; i++) {
				String type = "";
				int dateType = metaData.getColumnType(i + 1);
				if (Types.DATE == dateType) {
					type = "Date";
				} else if (Types.TIMESTAMP == dateType) {
					type = "DateTime";
				} else if (Types.TIME == dateType) {
					type = "Time";
				} else if (Types.VARCHAR == dateType) {
					type = "String";
				} else if (Types.INTEGER == dateType) {
					type = "Integer";
				} else if (Types.NUMERIC == dateType) {
					type = "Numeric";
				} else if (Types.DOUBLE == dateType) {
					type = "Double";
				} else if (Types.FLOAT == dateType) {
					type = "Float";
				} else {
					type = "String";
				}
				if (headerList != null) {
					headerList.add(metaData.getColumnName(i + 1));
				}
				if (typeList != null) {
					typeList.add(type);
				}
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			ps.close();
			connection.close();
		}
	}

	public void createTable(String table, ArrayList<FieldBean> fieldBeans)
			throws SQLException {
		Connection connection = dataSource.getConnection();
		String createTable = "create table " + table + "(";
		for (FieldBean fieldBean : fieldBeans) {
			createTable += "," + fieldBean;
		}
		createTable += ")";
		if (createTable.contains(",")) {
			createTable = createTable.replaceFirst(",", "");
		}
		PreparedStatement ps = connection.prepareStatement(createTable);
		try {
			ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			ps.close();
			connection.close();
		}
	}

	public HttpServletRequest getRequest() {
		return ((ServletRequestAttributes) RequestContextHolder
				.getRequestAttributes()).getRequest();
	}

	public HttpSession getSession() {
		return getRequest().getSession();
	}

	/**
	 * 获取当前用户
	 * 
	 * @return
	 */
	public CurrentUserBean getCurrentUserBean() {
		if (getSession().getAttribute("CurrentUserBean") != null) {
			return (CurrentUserBean) getSession().getAttribute(
					"CurrentUserBean");
		} else {
			return new CurrentUserBean();
		}
	}

	/**
	 * 根据用户编号查询用户所有的手机号码
	 * 
	 * @param userAccount
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> queryUserInfo(String userAccount)
			throws Exception {
		// 查询用户电话号码
		String sql = "select telphone,telphone1 from b_staff_info WHERE userAccount = ? UNION select telphone,'' from b_suppliercontact_info WHERE userAccount = ?";
		return querySqlToSQLResultBean(sql,
				new Object[] { userAccount, userAccount }).getMapList();
	}

	/**
	 * 根据角色、用户编号获取用户的电话信息
	 * 
	 * @param roleList
	 * @param userAccountList
	 * @return ArrayList<HashMap<String,Object>>roleList
	 * @throws Exception
	 */
	/*
	 * public ArrayList<HashMap<String,Object>> queryUserInfo(ArrayList<String>
	 * roleList,ArrayList<String> userAccountList) throws Exception{ //组装角色查询条件
	 * StringBuffer roleCodes = new StringBuffer(); if(roleList.size()>0){
	 * for(int i=0;i < roleList.size();i++){ if(i==0){ roleCodes.append("('");
	 * }else { roleCodes.append(",'"); } roleCodes.append(roleList.get(i));
	 * roleCodes.append("'"); } roleCodes.append(")"); }else{
	 * roleCodes.append("''"); } //组装用户编号查询条件 StringBuffer userAccounts = new
	 * StringBuffer(); if(userAccountList.size()>0){ for(int i=0;i <
	 * userAccountList.size();i++){ if(i==0){ userAccounts.append("('"); }else {
	 * userAccounts.append(",'"); } userAccounts.append(roleList.get(i));
	 * userAccounts.append("'"); } userAccounts.append(")"); }else{
	 * userAccounts.append("''"); } //到视图中条件查询用户电话号码 String sql =
	 * "select distinct userAccount, telphone,userName from view_userInfos where userAccount in "
	 * +userAccounts+" or roleCode in "+roleCodes; return
	 * querySqlToSQLResultBean(sql).getMapList(); }
	 */
	// /////////////////////////////////////
	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	// /////////////////
	@Override
	public Object add(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		return "";
	}

	@Override
	public Object del(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		return "";
	}

	@Override
	public Object upd(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		return "";
	}

	@Override
	public HashMap<String, Object> view(String guid) throws Exception {
		// TODO Auto-generated method stub
		return new HashMap<String, Object>();
	}
}
